﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using Soufun.DB;
using Soufun.Model;

namespace Soufun.DAL
{
    public class DProject
    {
        public  int Add(ProjectInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("Insert into tAreaAnalyze (");
            strSql.Append("AreaNameID,ProjectName,Amount,");
            strSql.Append("HousingSoldCount,HousingOnSaleCount,");
            strSql.Append("NonHousingSoldCount,NonHousingOnSaleCount,");
            strSql.Append("InsertTime)");
            strSql.Append(" value (");
            strSql.Append("@0,@1,@2,");
            strSql.Append("@3,@4,");
            strSql.Append("@5,@6,");
            strSql.Append("@7)");
            object[] paramaters = new object[]
                {
                    model.AreaNameID,
                    model.ProjectName,
                    model.Amount,
                    model.HousingSoldCount,
                    model.HousingOnSaleCount,
                    model.NonHousingSoldCount,
                    model.NonHousingOnSaleCount,
                    model.InsertTime
                };
            int result =DbHelper.ExecuteSql(strSql.ToString(), paramaters);
            return result;
        }

        public  DataSet GetListOfInsertTime(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  distinct insertTime from tAreaAnalyze ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by insertTime desc;");
            return DbHelper.Query(strSql.ToString());
        }

        public DataSet GetCountResultOfTwoDateTime(int areaId,string startTime,string endTime)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * ");             
            strSql.Append(" FROM tareaanalyze t where ");
            strSql.Append(" InsertTime = '");
            strSql.Append(startTime);              
            strSql.Append("'");
            if (areaId != 100)
            {
                strSql.Append(" and AreaNameID=");
                strSql.Append(areaId);
            }
            DataSet ds1 = DbHelper.Query(strSql.ToString());
            strSql.Clear();
            strSql.Append("SELECT * ");
            strSql.Append(" FROM tareaanalyze t where ");
            strSql.Append(" InsertTime = '");
            strSql.Append(endTime);
            strSql.Append("'");
            if (areaId != 100)
            {
                strSql.Append(" and AreaNameID=");
                strSql.Append(areaId);
            }
            DataSet ds2 = DbHelper.Query(strSql.ToString());

            if (ds1==null ||ds1.Tables.Count==0 || 
                ds2==null || ds2.Tables.Count==0)
            {
                return null;
            }

            DataSet dsResult = new DataSet();
            DataTable dt = new DataTable();
            dt.Columns.Add("ProjectName");
            dt.Columns.Add("a0");
            dt.Columns.Add("b0");
            dt.Columns.Add("a1");
            dt.Columns.Add("b1");
            dt.Columns.Add("a2");
            dt.Columns.Add("b2");
            dt.Columns.Add("c1");
            dt.Columns.Add("c2");
            dt.Columns.Add("a3");
            dt.Columns.Add("b3");
            dt.Columns.Add("a4");
            dt.Columns.Add("b4");

            foreach (DataRow row in ds2.Tables[0].Rows)
            {
                string strProjectName = row["ProjectName"].ToString();

                DataRow[] rows = ds1.Tables[0].Select("ProjectName ='"
                    +strProjectName+"'");
                if (rows.Length>0)
                {
                    int amountEnd = int.Parse(row["Amount"].ToString());
                    int houseingSoldCountEnd = int.Parse(row["HousingSoldCount"].ToString());
                    int housingOnSaleCountEnd = int.Parse(row["HousingOnSaleCount"].ToString());
                    int nonHousingSoldCountEnd = int.Parse(row["NonHousingSoldCount"].ToString());
                    int nonHousingOnSaleCountEnd = int.Parse(row["NonHousingOnSaleCount"].ToString());

                    int amountStart = int.Parse(rows[0]["Amount"].ToString());
                    int houseingSoldCountStart = int.Parse(rows[0]["HousingSoldCount"].ToString());
                    int housingOnSaleCountStart = int.Parse(rows[0]["HousingOnSaleCount"].ToString());
                    int nonHousingSoldCountStart = int.Parse(rows[0]["NonHousingSoldCount"].ToString());
                    int nonHousingOnSaleCountStart = int.Parse(rows[0]["NonHousingOnSaleCount"].ToString());

                    DataRow newRow = dt.NewRow();
                    newRow["ProjectName"] = strProjectName;
                    newRow["a0"] = amountStart;
                    newRow["a1"] = houseingSoldCountStart;
                    newRow["a2"] = housingOnSaleCountStart;
                    newRow["a3"] = nonHousingSoldCountStart;
                    newRow["a4"] = nonHousingOnSaleCountStart;

                    newRow["b0"] = amountEnd;
                    newRow["b1"] = houseingSoldCountEnd;
                    newRow["b2"] = housingOnSaleCountEnd;
                    newRow["b3"] = nonHousingSoldCountEnd;
                    newRow["b4"] = nonHousingOnSaleCountEnd;
                    int c1 = 0;
                    int c2 = 0;
                    if (houseingSoldCountEnd > houseingSoldCountStart)
                    {
                        c1 = houseingSoldCountEnd - houseingSoldCountStart;
                    }
                    if (nonHousingSoldCountEnd > nonHousingSoldCountStart)
                    {
                        c2 = nonHousingSoldCountEnd - nonHousingSoldCountStart;
                    }
                    newRow["c1"] = c1;
                    newRow["c2"] = c2;
                    dt.Rows.Add(newRow);
                }                 
            }
            dsResult.Tables.Add(dt);
            return dsResult;

            //StringBuilder strSql = new StringBuilder();
            //strSql.Append("SELECT ProjectName,");
            //strSql.Append("Max(Amount) as a0,");
            //strSql.Append("Min(Amount) as b0,");
            //strSql.Append("Max(HousingSoldCount) as a1,");
            //strSql.Append("Min(HousingSoldCount) as b1,");
            //strSql.Append("max(HousingOnSaleCount) as a2,");
            //strSql.Append("min(HousingOnSaleCount) as b2,");
            //strSql.Append("(max(HousingOnSaleCount) - min(HousingOnSaleCount)) as c1,");
            //strSql.Append("max(NonHousingSoldCount) as a3,");
            //strSql.Append("min(NonHousingSoldCount) as b3,");
            //strSql.Append("max(NonHousingOnSaleCount) as a4,");
            //strSql.Append("min(NonHousingOnSaleCount) as b4,");
            //strSql.Append("(max(NonHousingOnSaleCount)-min(NonHousingOnSaleCount)) as c2");
            //strSql.Append(" FROM tareaanalyze t where ");            
            //strSql.Append(" InsertTime = '");
            //strSql.Append(startTime);
            //strSql.Append("' or InsertTime='");
            //strSql.Append(endTime);
            //strSql.Append("'");
            //if (areaId!=100)
            //{
            //    strSql.Append(" and AreaNameID=");
            //    strSql.Append(areaId);
            //}
            //strSql.Append(" group by  ProjectName  order by id;");
            //DataSet ds = DbHelper.Query(strSql.ToString());
            //return ds;
        }


    }
}
